-- schema.ddl

create table diagnosis (
    id integer not null primary key,
    name varchar(400) not null unique
);

create table question (
    id integer not null primary key,
    name varchar(400) not null,
    category varchar(400) not null,
    type varchar(100) not null,
    unique (name, category)
);

create table answer (
    id integer not null primary key,
    q_id integer not null references question(id),
    name varchar(400) not null,
    unique (q_id, name)
);

create table test (
    id integer not null primary key,
    name varchar(2000) not null unique
);

create table test_question (
    id integer not null primary key,
    t_id integer not null references test(id),
    q_id integer not null references question(id)
);

create table test_case (
    tc_id integer not null,
    tq_id integer not null references test_question(id),
    answer_name varchar(400) not null,
    primary key(tc_id, tq_id)
);

create table test_case_result (
    tc_id integer not null references test_case(tc_id),
    diagnosis varchar(400) not null,
    cf integer not null
);
